package com.github.cakin.shiro.chapter11.dao;

import com.github.cakin.shiro.chapter11.JdbcTemplateUtils;
import com.github.cakin.shiro.chapter11.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import static com.github.cakin.shiro.chapter11.constant.Constant.*;

/**
 * @className: UserDaoImpl
 * @description: 用户Dao实现层
 * @date: 2020/5/19
 * @author: cakin
 */
public class UserDaoImpl implements UserDao {
    /**
     * 创建JdbcTemplate实例
     */
    private JdbcTemplate jdbcTemplate = JdbcTemplateUtils.jdbcTemplate();

    /**
     * 功能描述：创建用户
     *
     * @param user 用户
     * @return User 用户
     * @author cakin
     * @date 2020/5/19
     */
    public User createUser( final User user ) {
        final String sql = "insert into sys_users(username, password, salt, locked) values(?,?,?, ?)";

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement( Connection connection ) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[]{"id"});
                psst.setString(1, user.getUsername());
                psst.setString(NUM2, user.getPassword());
                psst.setString(NUM3, user.getSalt());
                psst.setBoolean(NUM4, user.getLocked());
                return psst;
            }
        }, keyHolder);

        user.setId(keyHolder.getKey().longValue());
        return user;
    }

    /**
     * 功能描述：更新用户
     *
     * @param user 用户
     * @author cakin
     * @date 2020/5/19
     */
    public void updateUser( User user ) {
        String sql = "update sys_users set username=?, password=?, salt=?, locked=? where id=?";
        jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getSalt(), user.getLocked(), user.getId());
    }

    /**
     * 功能描述：删除用户
     *
     * @param userId 用户Id
     * @author cakin
     * @date 2020/5/19
     */
    public void deleteUser( Long userId ) {
        String sql = "delete from sys_users where id=?";
        jdbcTemplate.update(sql, userId);
    }

    /**
     * 功能描述：添加用户-角色关系
     *
     * @param userId  用户id
     * @param roleIds 角色列表
     * @author cakin
     * @date 2020/5/19
     */
    public void correlationRoles( Long userId, Long... roleIds ) {
        if (roleIds == null || roleIds.length == 0) {
            return;
        }
        String sql = "insert into sys_users_roles(user_id, role_id) values(?,?)";
        for (Long roleId : roleIds) {
            if (!exists(userId, roleId)) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }
    }

    /**
     * 功能描述：移除用户-角色关系
     *
     * @param userId  用户id
     * @param roleIds 角色id列表
     * @author cakin
     * @date 2020/5/19
     */
    public void uncorrelationRoles( Long userId, Long... roleIds ) {
        if (roleIds == null || roleIds.length == 0) {
            return;
        }
        String sql = "delete from sys_users_roles where user_id=? and role_id=?";
        for (Long roleId : roleIds) {
            if (exists(userId, roleId)) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }
    }

    /**
     * 功能描述：判断userId，roleId 关系是否存在于sys_users_roles中
     *
     * @param userId 用户Id
     * @param roleId 角色Id
     * @return boolean
     * @author cakin
     * @date 2020/5/19
     * @description:
     */
    private boolean exists( Long userId, Long roleId ) {
        String sql = "select count(1) from sys_users_roles where user_id=? and role_id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userId, roleId) != 0;
    }

    /**
     * 功能描述：根据用户id查找一个用户
     *
     * @param userId 用户id
     * @return User 用户
     * @author cakin
     * @date 2020/5/19
     */
    public User findOne( Long userId ) {
        String sql = "select id, username, password, salt, locked from sys_users where id=?";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class), userId);
        if (userList.size() == 0) {
            return null;
        }
        return userList.get(0);
    }

    /**
     * 功能描述：根据用户名查找用户
     *
     * @param username 用户名
     * @return User 用户
     * @author cakin
     * @date 2020/5/19
     */
    public User findByUsername( String username ) {
        String sql = "select id, username, password, salt, locked from sys_users where username=?";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class), username);
        if (userList.size() == 0) {
            return null;
        }
        return userList.get(0);
    }

    /**
     * 功能描述：根据用户名查找其角色
     *
     * @param username 用户名
     * @return Set<String> 介绍集合
     * @author cakin
     * @date 2020/5/19
     */
    public Set<String> findRoles( String username ) {
        String sql = "select role from sys_users u, sys_roles r,sys_users_roles ur where u.username=? and u.id=ur.user_id and r.id=ur.role_id";
        return new HashSet(jdbcTemplate.queryForList(sql, String.class, username));
    }

    /**
     * 功能描述：根据用户名查找其权限
     *
     * @param username 用户名
     * @return Set<String> 权限集合
     * @author cakin
     * @date 2020/5/19
     */
    public Set<String> findPermissions( String username ) {
        //TODO 此处可以优化，比如查询到role后，一起获取roleId，然后直接根据roleId获取即可
        String sql = "select permission from sys_users u, sys_roles r, sys_permissions p, sys_users_roles ur, sys_roles_permissions rp "
                + "where u.username=? and u.id=ur.user_id and r.id=ur.role_id and r.id=rp.role_id and p.id=rp.permission_id";
        return new HashSet(jdbcTemplate.queryForList(sql, String.class, username));
    }
}
